#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
数据库管理模块
负责彩票数据的存储、查询和管理
"""

import sqlite3
import os
from datetime import datetime
import json
import logging

logger = logging.getLogger(__name__)

class DatabaseManager:
    def __init__(self, db_path='data/lottery.db'):
        self.db_path = db_path
        self.ensure_data_dir()
    
    def ensure_data_dir(self):
        """确保数据目录存在"""
        data_dir = os.path.dirname(self.db_path)
        if data_dir and not os.path.exists(data_dir):
            os.makedirs(data_dir)
    
    def init_database(self):
        """初始化数据库表结构"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        # 创建七星彩历史数据表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS qxc_history (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                period TEXT UNIQUE NOT NULL,
                draw_date TEXT NOT NULL,
                numbers TEXT NOT NULL,
                created_at TEXT DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        
        # 创建预测记录表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS predictions (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                lottery_type TEXT NOT NULL,
                predicted_numbers TEXT NOT NULL,
                prediction_date TEXT NOT NULL,
                algorithm_used TEXT NOT NULL,
                confidence_score REAL,
                created_at TEXT DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        
        conn.commit()
        # 创建中奖规则表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS qxc_prize_rules (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                prize_level TEXT NOT NULL,
                match_condition TEXT NOT NULL,
                prize_amount TEXT NOT NULL,
                description TEXT,
                created_at TEXT DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        
        # 插入七星彩中奖规则数据
        prize_rules = [
            ('一等奖', '7位全中', '浮动奖金，最高500万', '7位数字完全相同'),
            ('二等奖', '前6位或后6位全中', '浮动奖金，最高500万', '连续6位数字完全相同'),
            ('三等奖', '前5位或后5位全中', '3000元', '连续5位数字完全相同'),
            ('四等奖', '前4位或后4位全中', '500元', '连续4位数字完全相同'),
            ('五等奖', '前3位或后3位全中', '30元', '连续3位数字完全相同'),
            ('六等奖', '前2位或后2位全中', '5元', '连续2位数字完全相同')
        ]
        
        cursor.executemany('''
            INSERT OR IGNORE INTO qxc_prize_rules 
            (prize_level, match_condition, prize_amount, description)
            VALUES (?, ?, ?, ?)
        ''', prize_rules)
        
        conn.commit()
        conn.close()
        logger.info("数据库初始化完成")
    
    def insert_qxc_data(self, period, draw_date, numbers):
        """插入七星彩数据"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        try:
            # 使用空格分隔的格式存储，以便正确处理两位数的最后一位
            numbers_str = ' '.join(map(str, numbers))
            cursor.execute('''
                INSERT OR REPLACE INTO qxc_history 
                (period, draw_date, numbers)
                VALUES (?, ?, ?)
            ''', (period, draw_date, numbers_str))
            
            conn.commit()
            return True
        except Exception as e:
            logger.error(f"插入七星彩数据失败: {e}")
            return False
        finally:
            conn.close()
    
    def get_recent_data(self, lottery_type='qxc', limit=100):
        """获取最近的开奖数据"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        try:
            if lottery_type == 'qxc':
                cursor.execute('''
                    SELECT period, draw_date, numbers
                    FROM qxc_history
                    ORDER BY period DESC
                    LIMIT ?
                ''', (limit,))
                
                results = []
                for row in cursor.fetchall():
                    numbers_str = row[2]
                    
                    # 清理数据：移除所有非数字和空格的字符
                    import re
                    # 只保留数字和空格
                    numbers_str = re.sub(r'[^0-9\s]', ' ', numbers_str)
                    # 清理多余的空格
                    numbers_str = ' '.join(numbers_str.split())
                    
                    # 按空格分隔解析（标准格式）
                    if ' ' in numbers_str:
                        try:
                            numbers = [int(x) for x in numbers_str.split() if x.isdigit()]
                            if len(numbers) == 7:
                                results.append({
                                    'period': row[0],
                                    'draw_date': row[1],
                                    'numbers': numbers
                                })
                            else:
                                logger.warning(f"期号 {row[0]} 数据格式错误，期望7个数字，实际得到{len(numbers)}个")
                        except ValueError as e:
                            logger.error(f"期号 {row[0]} 数据解析失败: {e}")
                    else:
                        # 兼容旧格式：连续字符串（7位数字）
                        if len(numbers_str) == 7 and numbers_str.isdigit():
                            numbers = [int(numbers_str[i]) for i in range(7)]
                            results.append({
                                'period': row[0],
                                'draw_date': row[1],
                                'numbers': numbers
                            })
                        else:
                            logger.warning(f"期号 {row[0]} 数据格式不支持: '{numbers_str}'")
                
                return results
                
        except Exception as e:
            logger.error(f"查询数据失败: {e}")
            return []
        finally:
            conn.close()
    
    def get_all_data(self, lottery_type='qxc'):
        """获取所有历史数据"""
        return self.get_recent_data(lottery_type, limit=10000)
    
    def save_prediction(self, lottery_type, predicted_numbers, algorithm_used, confidence_score=0.0):
        """保存预测结果"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        try:
            cursor.execute('''
                INSERT INTO predictions 
                (lottery_type, predicted_numbers, prediction_date, algorithm_used, confidence_score)
                VALUES (?, ?, ?, ?, ?)
            ''', (lottery_type, json.dumps(predicted_numbers), 
                  datetime.now().isoformat(), algorithm_used, confidence_score))
            
            conn.commit()
            return True
        except Exception as e:
            logger.error(f"保存预测失败: {e}")
            return False
        finally:
            conn.close()
    
    def get_data_count(self, lottery_type='qxc'):
        """获取数据总数"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        try:
            table_name = 'qxc_history'
            cursor.execute(f'SELECT COUNT(*) FROM {table_name}')
            return cursor.fetchone()[0]
        except Exception as e:
            logger.error(f"查询数据总数失败: {e}")
            return 0
        finally:
            conn.close()
    
    def cleanup_old_records(self, lottery_type='qxc', keep_count=1000):
        """清理过期记录，只保留最新的指定数量"""
        try:
            conn = sqlite3.connect(self.db_path)
            cursor = conn.cursor()
            
            if lottery_type == 'qxc':
                # 获取总记录数
                cursor.execute('SELECT COUNT(*) FROM qxc_history')
                total_count = cursor.fetchone()[0]
                
                if total_count > keep_count:
                    # 删除最旧的记录
                    delete_count = total_count - keep_count
                    cursor.execute('''
                        DELETE FROM qxc_history 
                        WHERE id IN (
                            SELECT id FROM qxc_history 
                            ORDER BY period ASC 
                            LIMIT ?
                        )
                    ''', (delete_count,))
            
            deleted_rows = cursor.rowcount
            conn.commit()
            conn.close()
            
            return deleted_rows
            
        except Exception as e:
            logger.error(f"清理过期记录时发生错误: {str(e)}")
            return 0
    
    def backup_database(self, backup_file):
        """备份数据库"""
        try:
            import shutil
            shutil.copy2(self.db_path, backup_file)
            return True
        except Exception as e:
            logger.error(f"备份数据库时发生错误: {str(e)}")
            return False
    
    def get_database_stats(self):
        """获取数据库统计信息"""
        try:
            conn = sqlite3.connect(self.db_path)
            cursor = conn.cursor()
            
            # 获取七星彩记录数
            cursor.execute('SELECT COUNT(*) FROM qxc_history')
            qxc_count = cursor.fetchone()[0]
            
            # 获取预测记录数
            cursor.execute('SELECT COUNT(*) FROM predictions')
            prediction_count = cursor.fetchone()[0]
            
            conn.close()
            
            return {
                'qxc_records': qxc_count,
                'prediction_records': prediction_count
            }
            
        except Exception as e:
            logger.error(f"获取数据库统计信息时发生错误: {str(e)}")
            return None
    
    def calculate_prize_level(self, user_numbers, winning_numbers):
        """计算中奖等级"""
        if len(user_numbers) != 7 or len(winning_numbers) != 7:
            return None
            
        # 转换为字符串进行比较
        user_str = ''.join(map(str, user_numbers))
        winning_str = ''.join(map(str, winning_numbers))
        
        # 一等奖：7位全中
        if user_str == winning_str:
            return {'level': '一等奖', 'amount': '浮动奖金，最高500万'}
        
        # 二等奖：前6位或后6位全中
        if user_str[:6] == winning_str[:6] or user_str[1:] == winning_str[1:]:
            return {'level': '二等奖', 'amount': '浮动奖金，最高500万'}
        
        # 三等奖：前5位或后5位全中
        if user_str[:5] == winning_str[:5] or user_str[2:] == winning_str[2:]:
            return {'level': '三等奖', 'amount': '3000元'}
        
        # 四等奖：前4位或后4位全中
        if user_str[:4] == winning_str[:4] or user_str[3:] == winning_str[3:]:
            return {'level': '四等奖', 'amount': '500元'}
        
        # 五等奖：前3位或后3位全中
        if user_str[:3] == winning_str[:3] or user_str[4:] == winning_str[4:]:
            return {'level': '五等奖', 'amount': '30元'}
        
        # 六等奖：前2位或后2位全中
        if user_str[:2] == winning_str[:2] or user_str[5:] == winning_str[5:]:
            return {'level': '六等奖', 'amount': '5元'}
        
        return None
    
    def get_prize_rules(self):
        """获取中奖规则"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        try:
            cursor.execute('''
                SELECT prize_level, match_condition, prize_amount, description
                FROM qxc_prize_rules
                ORDER BY id
            ''')
            
            rules = []
            for row in cursor.fetchall():
                rules.append({
                    'level': row[0],
                    'condition': row[1], 
                    'amount': row[2],
                    'description': row[3]
                })
            return rules
            
        except Exception as e:
            logger.error(f"获取中奖规则失败: {e}")
            return []
        finally:
            conn.close()
    
    def save_prize_calculation(self, user_numbers, winning_numbers, prize_result, period=None):
        """保存中奖计算记录"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        try:
            # 创建中奖计算记录表（如果不存在）
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS prize_calculations (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    period TEXT,
                    user_numbers TEXT NOT NULL,
                    winning_numbers TEXT NOT NULL,
                    prize_level TEXT,
                    prize_amount TEXT,
                    calculation_date TEXT DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            
            # 插入计算记录
            cursor.execute('''
                INSERT INTO prize_calculations 
                (period, user_numbers, winning_numbers, prize_level, prize_amount)
                VALUES (?, ?, ?, ?, ?)
            ''', (period, 
                  ''.join(map(str, user_numbers)),
                  ''.join(map(str, winning_numbers)),
                  prize_result['level'] if prize_result else '未中奖',
                  prize_result['amount'] if prize_result else '0元'))
            
            conn.commit()
            return True
            
        except Exception as e:
            logger.error(f"保存中奖计算记录失败: {e}")
            return False
        finally:
            conn.close()

    def get_latest_period(self, lottery_type='qxc'):
        """获取最新期号"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        try:
            cursor.execute('''
                SELECT period FROM qxc_history
                ORDER BY period DESC
                LIMIT 1
            ''')
            result = cursor.fetchone()
            return result[0] if result else None
        except Exception as e:
            logger.error(f"查询最新期号失败: {e}")
            return None
        finally:
            conn.close()
    
    def check_qxc_winning(self, user_numbers, winning_numbers):
        """
        检查七星彩中奖情况
        
        Args:
            user_numbers: 用户选择的7位数字，格式如 "1234567"
            winning_numbers: 开奖号码的7位数字，格式如 "1234567"
            
        Returns:
            dict: 包含中奖信息的字典
        """
        if len(user_numbers) != 7 or len(winning_numbers) != 7:
            return {'error': '号码格式错误，必须是7位数字'}
            
        # 转换为列表便于比较
        user_list = list(user_numbers)
        winning_list = list(winning_numbers)
        
        # 检查各种中奖情况
        winning_info = {
            'is_winning': False,
            'prize_level': None,
            'prize_amount': None,
            'match_details': []
        }
        
        # 一等奖：7位全中
        if user_numbers == winning_numbers:
            winning_info.update({
                'is_winning': True,
                'prize_level': '一等奖',
                'prize_amount': '浮动奖金，最高500万',
                'match_details': ['7位数字完全相同']
            })
            return winning_info
            
        # 二等奖：前6位或后6位全中
        if (user_numbers[:6] == winning_numbers[:6] or 
            user_numbers[1:] == winning_numbers[1:]):
            winning_info.update({
                'is_winning': True,
                'prize_level': '二等奖',
                'prize_amount': '浮动奖金，最高500万',
                'match_details': ['连续6位数字相同']
            })
            return winning_info
            
        # 三等奖：前5位或后5位全中
        if (user_numbers[:5] == winning_numbers[:5] or 
            user_numbers[2:] == winning_numbers[2:]):
            winning_info.update({
                'is_winning': True,
                'prize_level': '三等奖',
                'prize_amount': '3000元',
                'match_details': ['连续5位数字相同']
            })
            return winning_info
            
        # 四等奖：前4位或后4位全中
        if (user_numbers[:4] == winning_numbers[:4] or 
            user_numbers[3:] == winning_numbers[3:]):
            winning_info.update({
                'is_winning': True,
                'prize_level': '四等奖',
                'prize_amount': '500元',
                'match_details': ['连续4位数字相同']
            })
            return winning_info
            
        # 五等奖：前3位或后3位全中
        if (user_numbers[:3] == winning_numbers[:3] or 
            user_numbers[4:] == winning_numbers[4:]):
            winning_info.update({
                'is_winning': True,
                'prize_level': '五等奖',
                'prize_amount': '30元',
                'match_details': ['连续3位数字相同']
            })
            return winning_info
            
        # 六等奖：前2位或后2位全中
        if (user_numbers[:2] == winning_numbers[:2] or 
            user_numbers[5:] == winning_numbers[5:]):
            winning_info.update({
                'is_winning': True,
                'prize_level': '六等奖',
                'prize_amount': '5元',
                'match_details': ['连续2位数字相同']
            })
            return winning_info
            
        # 未中奖
        return winning_info
        
    def get_prize_rules(self):
        """
        获取七星彩中奖规则
        
        Returns:
            list: 中奖规则列表
        """
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        try:
            cursor.execute('''
                SELECT prize_level, match_condition, prize_amount, description
                FROM qxc_prize_rules
                ORDER BY id
            ''')
            
            rules = []
            for row in cursor.fetchall():
                rules.append({
                    'prize_level': row[0],
                    'match_condition': row[1],
                    'prize_amount': row[2],
                    'description': row[3]
                })
                
            return rules
        except Exception as e:
            logger.error(f"获取中奖规则失败: {e}")
            return []
        finally:
            conn.close()

    def close(self):
        """关闭数据库连接"""
        if hasattr(self, 'conn') and self.conn:
            self.conn.close()